import sqlite3
import os
import time
import re
from FmsCommon import FmsCommon
class NasData:
    def __init__(self):
        # current_file_path = os.path.abspath(
        #     globals().get('__file__', os.getcwd()))
        fc =FmsCommon()
        self.root_dir  = fc.root_dir
        self.my_sqlite_file  = fc.my_sqlite_file
        
        
    def getSltKeySearch(self, param):
        test = param.get("nas_search_key")
        where = ""
        if test != "":
            nas_search_keys = param["nas_search_key"].split(" ")
            
            for key in nas_search_keys:
                if key != "":
                    key = key.replace("'", "")
                    where = where + f" and nas_search_key like '%{key}%'"

        where = where + "\n"
        test = self.removeRoot(param["abs_folder_path"])
        if test != "":
            where = where + f" and nas_abs_folder_path like '{test}%'\n"

        test = param.get("nas_file_ext")
        if test != "":
            where = where + f" and nas_file_ext='{test}'\n"
        
        test = param["safe"]
        if test != "":
            where = where + f" and nas_abs_folder_path not like'%{test}%'\n"
        test = param["nas_create_date"]
        if test != "":
            begin = param["nas_create_date_begin"]
            end = param["nas_create_date_end"]

            where = where + \
                f" and nas_create_date>='{begin}' and nas_create_date<='{end}'\n"
        offset = param.get("offset")
        if offset=='':
            offset=0
        
        sql = f"select * from t_nas_info where  1=1  {where}  LIMIT 25 OFFSET {offset}*25"
        
        conn = sqlite3.connect(self.my_sqlite_file)
        cursor = conn.cursor()
        result = cursor.execute(sql)
        json_result = []
        for row in result:
            json_result.append(
                dict(zip([column[0] for column in cursor.description], row)))
        return json_result
    
    def getNasInfosByFolder(self, nas_abs_folder_path):
        conn = sqlite3.connect(self.my_sqlite_file)
        sql = "select * from t_nas_info where nas_abs_folder_path=? order by nas_abs_file_path"
        cursor = conn.cursor()
        result = cursor.execute(sql, (self.removeRoot(nas_abs_folder_path),))
        json_result = []
        for row in result:
            json_result.append(
                dict(zip([column[0] for column in cursor.description], row)))

        result.close()
        cursor.close()
        conn.close()
        return json_result
    def getNasInfosBySame(self, nas_abs_file_path):
        
        conn = sqlite3.connect(self.my_sqlite_file)
        sql = "select * from t_nas_info t where exists (select 1 from t_nas_info t2 where t2.nas_file_md5 = t.nas_file_md5 and t2.nas_abs_file_path=?)"
        cursor = conn.cursor()
        result = cursor.execute(sql, (self.removeRoot(nas_abs_file_path),))
        json_result = []
        for row in result:
            json_result.append(
                dict(zip([column[0] for column in cursor.description], row)))

        result.close()
        cursor.close()
        conn.close()
        return json_result
    
    def delNasInfo(self, nas_abs_file_path):
        
        conn = sqlite3.connect(self.my_sqlite_file)
        sql = "delete from t_nas_info where nas_abs_file_path=?"
        c = conn.cursor()
        c.execute(sql, (self.removeRoot(nas_abs_file_path),))
        conn.commit()
        conn.close()
    def getNasFileExt(self):
        conn = sqlite3.connect(self.my_sqlite_file)
        sql = 'select distinct IFNULL(t.nas_file_ext,"null") nas_file_ext from t_nas_info t'
        cursor = conn.cursor()
        result = cursor.execute(sql)
        json_result = []
        for row in result:
            json_result.append(row[0])

        conn.commit()
        conn.close()
        return json_result
    def addNasInfo(self, item):

        conn = sqlite3.connect(self.my_sqlite_file)
        sql = """
        INSERT or REPLACE INTO t_nas_info 
        (nas_abs_folder_path,nas_abs_file_raw_path,nas_abs_file_path, nas_file_type,nas_create_date, nas_search_key,nas_file_md5,nas_file_ext,nas_version_date) 
        VALUES 
        (?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)
        """

        nas_abs_file_path = item.get("nas_abs_file_path")
        filename, file_extension = os.path.splitext(nas_abs_file_path)
        
        
        nas_file_ext = file_extension[1:].lower()
        create_time = os.path.getctime(item["nas_abs_file_path"])
        item["nas_create_date"] = time.strftime(
            '%Y-%m-%d %H:%M:%S', time.localtime(create_time))

        c = conn.cursor()
        c.execute(sql, (self.removeRoot(item.get("nas_abs_folder_path")),self.removeRoot(item.get("nas_abs_file_raw_path")), self.removeRoot(item.get("nas_abs_file_path")),
                         item.get("nas_file_type"),
                  item.get("nas_create_date"), item.get("nas_search_key"), item.get("nas_file_md5"),nas_file_ext))

        conn.commit()
        conn.close()
    def removeRoot(self,file_path):
        file_path = file_path.replace("\\\\","\\")
        pattern = rf"{self.root_dir}"
        replacement = ""
        # 使用正则表达式参数和 re.IGNORECASE 标志（Python 3.9+）
        new_text = self.case_insensitive_replace(file_path,pattern,replacement)
        
        
        
        return new_text
    def case_insensitive_replace(self,string, old, new):
        pattern = re.compile(re.escape(old), re.IGNORECASE)
        return pattern.sub(new, string)
if __name__ == "__main__":
    nd = NasData()
    p = nd.getNasFileExt( )
    print(p)
    
                    # d:\python\code\adb\nas\resource\文件夹1\IMG_20210704_122650.jpg